package com.emm.yixun.website.utils;

import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.emm.yixun.website.common.utils.DateUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.emm.yixun.website.task.utils.ExcelReader;

public class ExcelUtil {

	/**
	 * 导出到excel
	 */
	public static void exportExcel(List<List<Map<String, String>>> list, List<String[]> headerList, String[] name,HttpServletResponse response){
		try {
			  @SuppressWarnings("resource")
			  HSSFWorkbook wb = new HSSFWorkbook(); 
			  for(int n = 0; n < name.length; n++){
				  HSSFSheet sheet = wb.createSheet(); 
				  sheet.createFreezePane( 0, 1, 0, 1 );   
			      wb.setSheetName(n, name[n]);  
				  HSSFCellStyle headStyle = wb.createCellStyle(); 
				  HSSFFont f  = wb.createFont();      
				  f.setFontHeightInPoints((short) 15);// 字号   
			      f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗   
			      headStyle.setFont(f); 
			      headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
			      headStyle.setBorderBottom((short) 1);
			      headStyle.setBorderRight((short) 1);
			      headStyle.setFillBackgroundColor((short) 20);
			      HSSFRow row = sheet.createRow(0); 
			      //创建单元格，并设置值表头 设置表头居中  
			      HSSFCellStyle style = wb.createCellStyle();  
			      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
			      style.setBorderBottom((short) 1);
			      style.setBorderRight((short) 1);
			      
			      HSSFCell cell = null;
			      String[] header = headerList.get(n);
			      for (int i = 0; i < header.length; i++) { 
			    	  	sheet.setColumnWidth(i, header[i].getBytes().length*2*256);
			            cell = row.createCell(i);  
			            cell.setCellStyle(style);  
			            HSSFRichTextString text = new HSSFRichTextString(header[i]);  
			            cell.setCellValue(text);  
			      }
				  HSSFCellStyle contextstyle =wb.createCellStyle();
			      List<Map<String, String>> mapList = list.get(n);
		    	  for (int j = 0; j < mapList.size(); j++) {
		    		  Map<String, String> map = mapList.get(j);
		    		  row = sheet.createRow((int) j + 1);  
		    		  for (int i = 0; i < header.length; i++) {
			    		  cell = row.createCell(i);

			    		  if(map.get(header[i]+"_"+j) != null){

							  String data = map.get(header[i]+"_"+j);
							  cell.setCellValue(data.toString());
						  }else{
							  cell.setCellValue("");
						  }
		    		  }
				 }
			 }
			 SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
			 String downFileName = name[0]+df.format(new Date()) + ".xls";
			 downFileName = URLEncoder.encode(downFileName, "UTF-8"); 
			 response.reset();
			 response.setCharacterEncoding("utf-8");
			 response.setHeader("Content-Disposition","attachment;filename="+downFileName);
			 response.setContentType("application/vnd.ms-excel;charset=utf-8");  
			 OutputStream out = response.getOutputStream();
			 wb.write(out);
			 out.close();
			 out.flush();  
			 out.close();  
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出到excel
	 */
	public static void exportExcelForNumber(List<List<Map<String, String>>> list, List<String[]> headerList, List<String[]> headerNumberList, String[] name,HttpServletResponse response){
		try {
			@SuppressWarnings("resource")
			HSSFWorkbook wb = new HSSFWorkbook();
			for(int n = 0; n < name.length; n++){
				HSSFSheet sheet = wb.createSheet();
				sheet.createFreezePane( 0, 1, 0, 1 );
				wb.setSheetName(n, name[n]);
				HSSFCellStyle headStyle = wb.createCellStyle();
				HSSFFont f  = wb.createFont();
				f.setFontHeightInPoints((short) 15);// 字号
				f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
				headStyle.setFont(f);
				headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				headStyle.setBorderBottom((short) 1);
				headStyle.setBorderRight((short) 1);
				headStyle.setFillBackgroundColor((short) 20);
				HSSFRow row = sheet.createRow(0);
				//创建单元格，并设置值表头 设置表头居中
				HSSFCellStyle style = wb.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				style.setBorderBottom((short) 1);
				style.setBorderRight((short) 1);

				HSSFCell cell = null;
				String[] header = headerList.get(n);
				for (int i = 0; i < header.length; i++) {
					sheet.setColumnWidth(i, header[i].getBytes().length*2*256);
					cell = row.createCell(i);
					cell.setCellStyle(style);
					HSSFRichTextString text = new HSSFRichTextString(header[i]);
					cell.setCellValue(text);
				}

				HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式

				HSSFCellStyle intStyle =wb.createCellStyle();
//				intStyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
				intStyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点

				HSSFCellStyle datestyle =wb.createCellStyle();
				datestyle.setDataFormat(df.getFormat("yyyy/m/d"));

				List<Map<String, String>> mapList = list.get(n);
				for (int j = 0; j < mapList.size(); j++) {
					Map<String, String> map = mapList.get(j);
					row = sheet.createRow((int) j + 1);

					for (int i = 0; i < header.length; i++) {
						cell = row.createCell(i);
//						  cell.setCellValue(map.get(header[i]+"_"+j) == null ? "" : map.get(header[i]+"_"+j));
						if(map.get(header[i]+"_"+j) != null){
							String data = map.get(header[i]+"_"+j);
							try{
								//是否为日期类型
								Boolean isDate=false;//data是否为百分数
								isDate = DateUtils.isValidDate(data);
								HSSFCell contentCell = row.createCell(i);

								if (isDate){
									// 设置单元格内容为date类型
									cell.setCellValue(DateUtils.parseDate(data.toString()));

									// 设置单元格格式
									contentCell.setCellStyle(datestyle);

									// 设置单元格格式
									cell.setCellStyle(datestyle);
									continue;
								}

								if(headerNumberList == null && headerNumberList.size()>0){
									cell.setCellValue(data.toString());
									continue;
								}
								String[] headerNumbers = headerNumberList.get(0);
								//是否需要转换
								boolean isChange = false;
								for(String headerNumStr:headerNumbers){
									if(headerNumStr.equals(header[i])){
										isChange = true;
										break;
									}
								}
								if(!isChange){
									cell.setCellValue(data.toString());
									continue;
								}

								Boolean isNum = false;//data是否为数值型
								Boolean isInteger=false;//data是否为整数
								Boolean isPercent=false;//data是否为百分数

								if (data != null && StringUtils.isNotEmpty(data.trim())) {
									//判断data是否为数值型
									isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
									//判断data是否为整数（小数部分是否为0）
									isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
									//判断data是否为百分数（是否包含“%”）
									isPercent=data.toString().contains("%");
								}

								if (isInteger) {

									// 设置单元格格式
									contentCell.setCellStyle(intStyle);


									// 设置单元格内容为double类型
									cell.setCellValue(Long.parseLong(data.toString()));
									// 设置单元格格式
									cell.setCellStyle(intStyle);
								}else if (isNum){

									// 设置单元格格式
									contentCell.setCellStyle(intStyle);

									// 设置单元格内容为double类型
									cell.setCellValue(Double.parseDouble(data.toString()));
									// 设置单元格格式
									cell.setCellStyle(intStyle);
								}else{
//								  cell.setCellStyle(contextstyle);
									cell.setCellValue(data.toString());
								}


//							  contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
							}catch (Exception e){
								cell.setCellValue(data);
								e.printStackTrace();
							}




						}else{
							cell.setCellValue("");
						}
					}
				}
			}
			SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
			String downFileName = name[0]+df.format(new Date()) + ".xls";
			downFileName = URLEncoder.encode(downFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-Disposition","attachment;filename="+downFileName);
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**

	 * 判断是否是整数或者是小数

	 * @param str

	 * @return true：是，false不是

	 */

	private static boolean validateNumber(String str) {

		if(StringUtils.isBlank(str)) {
			return false;
		}

		// 说明一下的是该正则只能识别4位小数；如果不限制小数位数的话，写成[+-]?[0-9]+(\\.[0-9]+)?就可以了

//		return str.matches("[+-]?[0-9]+(\\.[0-9]{1,4})?");
		return str.matches("[+-]?[0-9]+(\\.[0-9]+)?");
	}
	/**
	 * 导入excel
	 */
	public static List<Map<String, String>> importExcel(InputStream file, String fileType){
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} 
			Sheet sheet1 = wb.getSheetAt(0);
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				
				Map<String, String> map = new HashMap<String, String>();
				Integer totalCell = (int) row.getLastCellNum(); //获取excel列数
				for (int j = 0; j < totalCell; j++) { 
					String cell = ExcelReader.getCell(row.getCell(j));
					map.put("excel_"+j, cell);
				}
				list.add(map);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return list;
	}
}
